package xin.printk.android.data.db;

public class SQL {

    static final String DATABASE = "android.db";

    public static final String TABLE_USER = "user";

    public static final String TABLE_RESUME = "resume";

    public static final String TABLE_EXPERIENCE = "experience";

    public static final String TABLE_PLAN = "plan";

    static String createUserTable = "create table if not exists " + TABLE_USER
            + "("
            + "id integer primary key autoincrement,"
            + "account varchar(16) not null,"
            + "password varchar(24) not null,"
            + "username varchar(64) not null,"
            + "avatar varchar(255),"
            + "phone varchar(16),"
            + "telephone varchar(24),"
            + "introduction varchar(255),"
            + "company varchar(255),"
            + "job varchar(64),"
            + "address varchar(255)"
            + ")";

    static String insertDefaultUserRecord = "insert into " + TABLE_USER
            + " (id, account, password, username)"
            + " values (0, \"0\", \"0\", \"root\")";

    static String createPlanTable = "create table if not exists " + TABLE_PLAN
            + "("
            + "id integer primary key autoincrement,"
            + "user_id integer not null,"
            + "status integer not null,"
            + "level integer not null,"
            + "title varchar(127) not null,"
            + "content varchar(511),"
            + "create_time long,"
            + "plan_time long,"
            + "completed_time long,"
            + "rating float,"
            + "comment varchar(255),"
            + "foreign key (user_id) references " + TABLE_USER + "(id)"
            + ")";

    public static String queryCompletedPlans = "select * from " + TABLE_PLAN
            + " where status = 1 order by completed_time desc limit 10 offset ";

    public static String queryUndonePlans = "select * from " + TABLE_PLAN
            + " where status = 0 order by create_time desc limit 10 offset ";

    public static String queryPlan = "select * from " + TABLE_PLAN
            + " where id = ?";
}
